# coding: utf-8
import pymysql.cursors
import time
import datetime
i = 1
sum = 0


class PdbcUtil():
    def __init__(self, host='117.51.142.180', db='kch2020'):
        self.conn = pymysql.connect(host=host, user='kch2020', password='&-bh14', db='mqtt', charset='utf8',port=4306)
        self.cursor = self.conn.cursor()

    def insertData2(self,sensorId='hahah',RH=0.0,temp=0.0,light=0.0):
        currentTime = int(time.time())
        now = datetime.datetime.now()
        now = now.strftime("%Y-%m-%d %H:%M:%S")

        sql = "insert into data(createdAt,sensorId,RH,temp,light)values(\"%s\",\"%s\",%f,%f,%f);" % (now,sensorId,RH,temp,light)

        print(sql)
        self.cursor.execute(sql)
        self.conn.commit()
    def insertData(self,topic,message,qos):
        currentTime = int(time.time())
        now = datetime.datetime.now()
        now = now.strftime("%Y-%m-%d %H:%M:%S")

        sql = "insert into mqtt(createdAt,topic,message,qos)values(\"%s\",\"%s\",\"%s\",NULL);" % (now,topic,message)
        print(sql)
        self.cursor.execute(sql)
        self.conn.commit()
    def insertRHAndTempData(self, temp: float, RH: float, CO2: float, TVOC: float, t=0):
        currentTime = 0
        if (t == 0):
            currentTime = int(time.time())
        else:
            currentTime = t
        sql = "insert into sample(samplingtime,temp,RH,CO2,TVOC)values(%d,%f,%f,%f,%f);" % (
            currentTime, temp, RH, CO2, TVOC)
        self.cursor.execute(sql)
        self.conn.commit()

    def insertEventData(self, eventType: str):
        currentTime = int(time.time())
        sql = "insert into events(eventtime,eventType)values(%d,\'%s\');" % (currentTime, eventType)
        self.cursor.execute(sql)
        self.conn.commit()

    def getSampleData(self):
        currentTime = int(time.time())
        sql = "select temp from sample;"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()

        self.conn.commit()
        f=open('/home/hzy/Documents/Development/python/buaa/files/temp.csv','w')
        for i in result[100:]:
            f.write('%f,\n'%i[0])
        print(result)
        f.close()


if __name__ == '__main__':
    pu = PdbcUtil()
    pu.insertData2()
    # pu.getSampleData()
    #pu.insertRHAndTempData(temp=100 / 3, RH=100 / 3, CO2=2000 / 3, TVOC=200 / 3)
    # pu.insertEventData(eventType='Turned On!')
